package cn.lil.dao;

import cn.lil.model.Order;
import cn.lil.model.OrderItem;
import cn.lil.utils.DataSourceUtils;
import com.oracle.wls.shaded.org.apache.xpath.operations.Or;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class OrderDao {
    //    添加购物车
    public void insertOrder(Order order) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "insert into `order`(total,amount,status,pay_type,user_id,datetime) values(?,?,?,?,?,?)";
        r.update(sql,
                order.getTotal(), order.getAmount(), order.getStatus(), order.getPayType(), order.getUserId(), order.getDatetime());
    }

    //    添加购物项
    public void insertOrderItem(OrderItem orderItem) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "insert into orderitem(price,amount,bearing_id,order_id) values(?,?,?,?)";
        r.update(sql, orderItem.getPrice(), orderItem.getAmount(), orderItem.getBearingId(), orderItem.getOrderId());
    }

    //    更新当前order的总价和商品总数
    public void updateOrderAmountAndtotal(int orderId, int userId, int amount, float total) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "update `order` set total=?,amount=? where id=? and user_id=?";
        r.update(sql, total, amount, orderId, userId);
    }

    //    更新某一个购物项的数量和总价
    public void updateOrderItemAmountAndPrice(int orderId, int bearingId, int amount, float price) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "update orderitem set price=?,amount=? where order_id=? and bearing_id=?";
        r.update(sql, price, amount, orderId, bearingId);
    }


    //    查询当前用户下的购物车
    public List<Order> selectAll(int userId) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select * from `order` where user_id = ? and status=0 order by datetime desc";
        return r.query(sql, new BeanListHandler<Order>(Order.class), userId);
    }

    //    查询单个购物车下的所有购物项
    public List<OrderItem> selectAllitem(int orderId) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select o.id,o.price,o.amount,b.type,o.bearing_id AS bearingId,o.order_id AS orderId from" +
                " orderitem o,bearing b where o.order_id = ? and o.bearing_id = b.id";
        return r.query(sql, new BeanListHandler<OrderItem>(OrderItem.class), orderId);
    }

    //    查询当前购物车的条数
    public int getOrderCount(int status) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "";
        if (status == 0) {
            sql = "select count(*) from `order`";
            return r.query(sql, new ScalarHandler<Long>()).intValue();
        } else {
            sql = "select count(*) from `order` where status = ?";
            return r.query(sql, new ScalarHandler<Long>(), status).intValue();
        }
    }

    //    根据用户id查询order列表
    public List<Order> selectOrderList(int status, int uid) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        if (status == 0) {
            String sql = "select o.id,o.total,o.amount,o.status,o.pay_type AS payType,o.datetime,u.username,u.phone_number,u.address" +
                    " from `order` o,users u where o.user_id=u.id and u.id=? order by o.datetime desc";
            return r.query(sql, new BeanListHandler<Order>(Order.class), uid);
        } else {
            String sql = "select o.id,o.total,o.amount,o.status,o.pay_type AS payType,o.datetime,u.username,u.phone_number,u.address,u.id AS userId" +
                    " from `order` o,users u where o.user_id=u.id and o.status=? and u.id=? order by o.datetime desc";
            return r.query(sql, new BeanListHandler<Order>(Order.class), status, uid);
        }
    }

    //    更新状态为提交
    public void updateStatus(int id) throws SQLException {
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String formattedDate = sdf.format(date);
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "update `order` set status=1,datetime=? where id=?";
        r.update(sql, formattedDate, id);
    }

    //    删除购物车
    public void deleteOrder(int id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "delete from `order` where id=?";
        r.update(sql, id);
    }

    //    删除单个购物车中的单个购物项
    public void deleteOrderItem(int id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "delete from orderitem where id=?";
        r.update(sql, id);
    }

    public int getLastInsertId() throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select last_insert_id()";
        BigInteger bi = r.query(sql, new ScalarHandler<BigInteger>());
        return Integer.parseInt(bi.toString());
    }
}
